<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>query_script_foreach: common_schema documentation</title>
	<meta name="description" content="query_script_foreach: common_schema" />
	<meta name="keywords" content="query_script_foreach: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="query_script_foreach.html">query_script_foreach</a></h2>	

<p>
QueryScript Flow Control: <strong>foreach</strong> statement
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>
foreach ($var1 [, $var2...] : <i>collection</i>)
{
  statement;
  statement, typically using $var1 [, $var2...];
}
[otherwise
  statement;]
</pre></blockquote>
</p>

<h3>DESCRIPTION</h3>
<p> 
<i>foreach</i> is a flow control looping structure. It iterates <i>collections</i> of various 
types, assigns iterated values onto variables, and executes statements per iteration.
</p>
<p>
<i>foreach</i> iterates all elements in given collection, and executes a statement (or block of statements) per element. 
<i>foreach</i> terminates when all elements have been processed. The <a href="query_script_break.html">break</a> and 
<a href="query_script_return.html">return</a> statements also break iteration execution.
</p>
<p>
As opposed to the <a href="foreach.html">foreach()</a> routine, <i>foreach</i> loops can be nested within other flow control structures,
such as 
<a href="query_script_if_else.html">if-else</a>,
<a href="query_script_while.html">while</a>,
<a href="query_script_loop_while.html">loop-while</a> and
<i>foreach</i> itself (see examples below).
read more about the difference between the <i>foreach</i> control flow and the <i>foreach()</i> routine <a href="#notes">following</a>.
</p>
<p>
	The <strong>otherwise</strong> clause is optional. It will execute once should no iteration
	take place. That is, if at least one <strong>collection</strong> element exists,
	<strong>otherwise</strong> is skipped.
</p>
<h4>Variables</h4>
<p>
The <i>foreach</i> construct is also a <a href="query_script_variables.html">variable</a> declaration point. Variables are created
and are assigned to, per loop iteration. As with the <a href="query_script_var.html">var</a> statement, at least one variable is expected
on <i>foreach</i> clause.
</p>
<p>
Any variables declared in the <i>foreach</i> clause are assigned with values as per iteration element.
There are various types of collections, as described following. These can vary from SELECT statements to number ranges.
Elements within collections can be single-valued or multi-valued. To illustrate, consider these examples: 

<blockquote><pre>
foreach($counter: 1:1024)
{
  -- Collection is of numbers-range type. It is single valued.
  -- Do something with $counter
}
</pre></blockquote>
In the above example, a single variable, named <strong>$counter</strong> is declared. It is assigned with the integer values 
<strong>1</strong>, <strong>2</strong>, <strong>3</strong>, ..., <strong>1024</strong>, one value per iteration. 
<blockquote><pre>
foreach($name, $pop: SELECT Name, Population FROM world.Country)
{
  -- Collection is of query type. It may hold up to <strong>9</strong> values (generated by <strong>9</strong> first columns).
  -- Do something with $name and $pop.
}
</pre></blockquote>
In this example, the <strong>SELECT</strong> query returns <strong>2</strong> columns: <strong>Name</strong> and <strong>Population</strong>, 
which we assign onto the variables <strong>$name</strong> and <strong>$pop</strong>, respectively. This happens per row in query's result set.
</p>
<p>
At least one variable must be declared. However, it is OK to list less than the amount of variables available from the collection. Hence, the following
is valid:
<blockquote><pre>
foreach($name: SELECT Name, Population FROM world.Country)
{
  -- Do something with $name. We never bother to actually read the <strong>Population</strong> value.
}
</pre></blockquote>
</p>

<p>
Variable <a href="query_script_variables.html#expansion">expansion</a> is of importance, as it allows using variable names in place of constants which are otherwise non-dynamic,
such as schema or table names. Read more on <a href="query_script_variables.html">Variables</a> before checking up on the examples. 
</p>

<h4>Collection variables</h4>
<p>
	Collections (described following) may themselves use local variables, as well as expanded
	variables.
</p>

<h4>Collections</h4>
<p>
<i>foreach</i> accepts several types of collections. They are automatically recognized by their
pattern. The following collections are recognized (also see EXAMPLES section below):
<ul>
	<li><strong>A SELECT query</strong>: any <strong>SELECT</strong> statement makes for a collection,
		which is the result set of the query.
		<br/>Each row in the result set is an element. 
		<br/>The query must specify result columns. That is, a <strong>SELECT *</strong> query is not valid.
		<br/>Otherwise any SELECT query is valid, with any result set. However, only first <strong>9</strong> 
		columns in the result set can be assigned to variables. Variables are matched to columns by order of definition.
		<br/>Column values are treated as text, even though they may originally be of other types.
	</li>
	<li><strong>Numbers range</strong>: a range of integers, both inclusive, e.g. <strong>'1970:2038'</strong>.
		<br/>Negative values are allowed. The first (left) value should be smaller or equal to the second (right) value,
		or else no iteration is performed.
		<br/>One variable is assigned with value on this collection.
	</li>
	<li><strong>Two dimensional numbers range</strong>: a double range of integers, e.g. <strong>'-10:10,1970:2038'</strong>.
		<br/>Each one of the ranges answers to the same rules as for a single range.
		<br/>There will be <strong>m * n</strong> iterations on ranges of size <strong>m</strong> and <strong>n</strong>. For example,
		in the sample range above there will be <strong>11 * 69</strong> iterations (or elements).
		<br/>Two variables are assigned with values on this collection.
		<br/>This type of collection is maintained in compatibility with the <a href="foreach.html">foreach()</a> routine.
		Script-wise, though, it is perfectly possible to nest two <i>foreach</i> number-ranges loops.
	</li>
	<li><strong>A constants set</strong>: a predefined set of constant values, e.g. <strong>'{red, green, blue}'</strong>.
		<br/>Constants are separated by either spaces or commas (or both). 
		<br/>Constants can be quoted so as to allow spaces or commas within constant value. Quotes themselves are discarded.
		<br/>Empty constants are discarded.
		<br/>One variable is assigned with value on this collection.
	</li>
	<li><strong>'schema'</strong>: this is the collection of available schemata (e.g. as with <strong>SHOW DATABASES</strong>).
		<br/>One variable is assigned with value on this collection. This value is the name of the schema.
	</li>
	<li><strong>'schema like <i>expr</i>'</strong>: databases whose names match the given <strong>LIKE</strong> expression.
		<br/>One variable is assigned with value on this collection. This value is the name of the schema.
	</li>
	<li><strong>'schema ~ <i>'regexp'</i>'</strong>: databases whose names match the given regular expression.
		<br/>One variable is assigned with value on this collection. This value is the name of the schema.
	</li>
	<li><strong>'table in <i>schema_names</i>'</strong>: collection of all tables in given schema. Only tables are included: views are not listed.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		given schema.
		<br/>An element of this collection has <strong>4</strong> values, which can be mapped up to <strong>4</strong> variables:
		<ol>
			<li>Table name</li>
			<li>Schema name</li>
			<li>Storage engine name</li>
			<li>Table's create options</li>
		</ol>
	</li>
	<li><strong>'table like <i>expr</i>'</strong>: all tables whose names match the given <strong>LIKE</strong> expression. These can be tables
		from different databases/schemata.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
		<br/>An element of this collection has <strong>4</strong> values, which can be mapped up to <strong>4</strong> variables:
		<ol>
			<li>Table name</li>
			<li>Schema name</li>
			<li>Storage engine name</li>
			<li>Table's create options</li>
		</ol>
	</li>
	<li><strong>'table ~ <i>'regexp'</i>'</strong>: all tables whose names match the given regular expression. These can be tables
		from different databases/schemata.
		<br/>This syntax is <strong>INFORMATION_SCHEMA</strong> friendly, in that it only scans and opens <strong>.frm</strong> files for
		a single schema at a time. This reduces locks and table cache entries, while potentially taking longer to complete.
		<br/>An element of this collection has <strong>4</strong> values, which can be mapped up to <strong>4</strong> variables:
		<ol>
			<li>Table name</li>
			<li>Schema name</li>
			<li>Storage engine name</li>
			<li>Table's create options</li>
		</ol>
	</li>
</ul>
Any other type of input raises an error.
</p>


<p>
Following is a brief sample of valid <i>foreach</i> expressions:
<table>
	<tr><th>Collection type</th><th>Example of valid input</th></tr>
	<tr><td>SELECT query</td><td>$id, $n: SELECT id, name FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time &gt; 20</td></tr>
	<tr><td>Numbers range</td><td>$year: 1970:2038</td></tr>
	<tr><td>Two dimensional numbers range</td><td>$hr, $minute: 0:23,0:59</td></tr>
	<tr><td>Constants set</td><td>$country: {USA, "GREAT BRITAIN", FRA, IT, JP}</td></tr>
	<tr><td>'schema'</td><td>$schema_name: schema</td></tr>
	<tr><td>'schema like <i>expr</i>'</td><td>$customer_schema_name: schema like customer_%</td></tr>
	<tr><td>'schema ~ <i>'regexp'</i>'</td><td>$customer_schema_name: schema ~ '^customer_[0-9]+$'</td></tr>
	<tr><td>'table in <i>schema_name</i>'</td><td>$table_name, $schema_name, $engine, $create_options: table in sakila</td></tr>
	<tr><td>'table like <i>expr</i>'</td><td>$table_name, $schema_name, $engine: table like wp_%</td></tr>
	<tr><td>'table ~ <i>'regexp'</i>'</td><td>$table_name, $schema_name: table ~ '^state_[A-Z]{2}$'</td></tr>
</table>
</p>


<h3>EXAMPLES</h3>
<ul>
	<li>SELECT query
		<p>
		Kill queries for user <strong>'analytics'</strong>. 
		<blockquote><pre>
foreach($id: SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user = 'analytics') 
  KILL QUERY :$id;
</pre></blockquote>
		(But see also <a href="killall.html">killall()</a>)
		</p>
		<p>
		Select multiple columns; execute multiple queries based on those columns:
		</p>
		<blockquote><pre>
foreach($code, $name: SELECT Code, Name FROM world.Country WHERE Continent='Europe')
{ 
  DELETE FROM world.CountryLanguage WHERE CountryCode = $code; 
  DELETE FROM world.City WHERE CountryCode = $code; 
  DELETE FROM world.Country WHERE Code = $code; 
  INSERT INTO test.logs (msg) VALUES (CONCAT('deleted country: name=', $name));
}
</pre></blockquote>		
	</li>
	
	<li>Numbers range:
		<p>
		Delete records from July-August for years <strong>2001</strong> - <strong>2009</strong>:
		</p>
		<blockquote><pre>
foreach($year: 2001:2009) 
  DELETE FROM sakila.rental WHERE rental_date >= CONCAT($year, '-07-01') AND rental_date < CONCAT($year, '-09-01');
</pre></blockquote>		

		<p>
		Generate tables:
		</p>
		<blockquote><pre>
foreach($i: 1:8)
  CREATE TABLE test.t_:${i} (id INT);
		
SHOW TABLES FROM test;
+----------------+ 
| Tables_in_test | 
+----------------+ 
| t_1            |
| t_2            |
| t_3            |
| t_4            |
| t_5            |
| t_6            |
| t_7            |
| t_8            |
+----------------+ 		
</pre></blockquote>		
	</li>
	
	
	<li>Constants set:
		<p>
		Generate databases:
		</p>
		<blockquote><pre>
foreach($shard: {US, GB, Japan, FRA})
  CREATE DATABASE dbshard_:${shard};

show databases LIKE 'dbshard_%';
+----------------------+
| Database (dbshard_%) |
+----------------------+
| dbshard_FRA          |
| dbshard_GB           |
| dbshard_Japan        |
| dbshard_US           |
+----------------------+
</pre></blockquote>		
	</li>

	<li>'schema':
		<p>
		List full tables on all schemata:
		</p>
		<blockquote><pre>
foreach($scm: schema)
  SHOW FULL TABLES FROM :$scm;
+---------------------------------------+-------------+
| Tables_in_information_schema          | Table_type  |
+---------------------------------------+-------------+
| CHARACTER_SETS                        | SYSTEM VIEW |
| COLLATIONS                            | SYSTEM VIEW |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW |
| COLUMNS                               | SYSTEM VIEW |
| COLUMN_PRIVILEGES                     | SYSTEM VIEW |
...
+---------------------------------------+-------------+

...
		
+-----------------+------------+
| Tables_in_world | Table_type |
+-----------------+------------+
| City            | BASE TABLE |
| Country         | BASE TABLE |
| CountryLanguage | BASE TABLE |
| Region          | BASE TABLE |
+-----------------+------------+
</pre></blockquote>		
	</li>
	
	<li>'schema like <i>expr</i>':
		<p>
		Create a new table in all hosted WordPress schemata:
		</p>
		<blockquote><pre>
foreach($scm: schema like wp%)
{
  CREATE TABLE :$scm.wp_likes(id int, data VARCHAR(128));
}
		</pre></blockquote>
	</li>
	
	<li>'schema ~ <i>'regexp'</i>':
		<p>
		Likewise, be more accurate on schema name:
		</p>
		<blockquote><pre>
foreach ($scm: schema ~ '^wp_[\d]+$') 
{
  CREATE TABLE :$scm.wp_likes(id int, data VARCHAR(128));
}
		</pre></blockquote>
	</li>
	
	<li>'table in <i>schema_name</i>':
		<p>
		Compress InnoDB tables in <strong>sakila</strong>. Leave other engines untouched.
		</p>
		<blockquote><pre>
foreach($table, $schema, $engine: table in sakila)
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;
		</pre></blockquote>
	</li>

	<li>'table like <i>expr</i>':
		<p>
		Add a column to all <strong>wp_posts</strong> tables in hosted WordPress databases:
		</p>
		<blockquote><pre>
foreach($tbl, $scm: table like wp_posts)
  ALTER TABLE :$scm.:$tbl ADD COLUMN post_geo_location VARCHAR(128);
		</pre></blockquote>
	</li>

	<li>'table ~ <i>'regexp'</i>':
		<p>
		Add a column to tables whose name matches the given regular expression, in any database:
		</p>
		<blockquote><pre>
foreach ($tbl, $scm: table ~ '^customer_data_[\d]+$')
  ALTER TABLE :$scm.:$tbl ADD COLUMN customer_geo_location VARCHAR(128);
		</pre></blockquote>
	</li>

	<li>Use an <strong>otherwise</strong> clause:
		<p>
		The following collection will not match any table:
		</p>
		<blockquote><pre>
foreach ($table, $schema: table like non_existing_table_name_%)
  select $table, $schema;
otherwise
  echo 'No tables found';

+-------------------+
| echo              |
+-------------------+
| 'No tables found' |
+-------------------+  
  		</pre></blockquote>
	</li>
</ul>

<a name="notes"></a>
<h3>NOTES</h3>
<h4>foreach vs. foreach()</h4>
<p>
The <i>foreach</i> flow control structure and the <a href="foreach.html">foreach()</a> routine both iterate collections, accept similar
(but not identical) collection syntax, and invoke scripts per loop iteration. They share some similar use cases, but are nevertheless different.
</p>
<p>
Very briefly, in Geekish, the differences are:
<ul>
	<li><i>foreach()</i> is more META.</li>
	<li><i>foreach()</i> cannot be nested, nor called from within a script.</li>
	<li><i>foreach</i> is more limited in terms of what it can do with iterated values.</li>
</ul>
To fully understand the differences, one must first be acquainted with <a href="query_script_variables.html#expansion">variables expansion</a>
. Consider the following example:</p>

<p>
<ul>
	<li>
		<p>
		<i>foreach() routine</i>
		<blockquote><pre>mysql&gt; call foreach(
	'2001:2009', 
	"DELETE FROM sakila.rental WHERE rental_date >= '${1}-07-01' AND rental_date < '${1}-09-01'");
</pre></blockquote>
		Iterated values are integers in the range <strong>2001 - 2009</strong>. The placeholder <strong>${1}</strong>
		is assigned with iterated value. The script (a single query in this case) never sees <strong>${1}</strong> because
		the text of the script gets manipulated before being invoked. Thus, there are <strong>9</strong> different looking scripts
		invoked. For example, the second iteration would execute the following script:
		<blockquote><pre>DELETE FROM sakila.rental WHERE rental_date >= '2002-07-01' AND rental_date < '2002-09-01'
		</pre></blockquote>
		Since placeholders make for text manipulation of the script even before it is invoked, the options are limit-less.
		There is no constraint on what can or cannot be used as placeholder, as long as the resulting manipulated text makes for a valid script.
		</p>
		
		<p>
		<i>foreach</i> flow control structure:
		<blockquote><pre>
foreach($year: 2001:2009) 
  DELETE FROM sakila.rental WHERE rental_date >= CONCAT($year, '-07-01') AND rental_date < CONCAT($year, '-09-01');
</pre></blockquote>
		The <strong>$year</strong> variable has the same limitations as any user-defined variable. It cannot be used from within a quoted text:
		<strong>'$year-07-01'</strong> is just a string, and the fact the term <strong>$year</strong> appears inside this quoted text means nothing.
		Hence, we must use <strong>CONCAT</strong> in order to build the date.		
		</p>
	</li>
	</ul>
</p>

<h3>SEE ALSO</h3>
<a href="query_script_variables.html">Variables</a>,
<a href="query_script_while.html">while</a>,
<a href="query_script_break.html">break</a>,
<a href="foreach.html">foreach()</a>

<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
